<?php
$db = mysql_connect(DBHOST, DBUSER, DBPASS) or die('Failed to connect to database');
if (isset($_GET['force_initialize']) || !mysql_select_db(DBNAME, $db)) {
    if (!db_initialize()) {
        dir('Failed to initialize database, check configuration');
    }
}

if (isset($_GET['force_import'])) {
    readOldConfigToDatabase();
}

function get_record($id) {
    if (!is_numeric($id)) {
        die('Invalid ID specified.');
    }
    $sql = "
    	SELECT `title`, `url`, `active`, `comment`, `source`, `config`, `autologin`
    	FROM `record`
    	WHERE `id`=$id
    	LIMIT 1
    ";
    $res = safe_query($sql);
    if (!$res || mysql_num_rows($res) == 0) {
        return false;
    }
    $record = mysql_fetch_assoc($res);
    $record['hosts'] = array();
    $record['domains'] = array();
    $record['extra'] = array();

    $sql = "SELECT `host`, `js` FROM `host` WHERE `record_id`=" . $id;
    $res = safe_query($sql);
    while ($row = mysql_fetch_assoc($res)) {
        $record['hosts'][] = array($row['host'], $row['js']);
    }
    $sql = "SELECT `domain`, `js` FROM `domain` WHERE `record_id`=" . $id;
    $res = safe_query($sql);
    while ($row = mysql_fetch_assoc($res)) {
        $record['domains'][] = array($row['domain'], $row['js']);
    }
    $sql = "SELECT `key`, `value` FROM `extra` WHERE `record_id`=" . $id . " ORDER BY `seq` ASC";
    $res = safe_query($sql);
    while ($row = mysql_fetch_assoc($res)) {
        $record['extra'][] = array($row['key'], $row['value']);
    }
    return $record;
}

function update_item($id) {
    if (!is_numeric($id)) {
        die('Non-numeric item record id "' . $id . '".');
    }
    if (pv('delete') == 'Delete') {
        $sql = "
			DELETE FROM `record`
			WHERE `id`=" . $id . "
		";
        safe_query($sql);
        return;
    }
    strip_array($_POST);
    mres_array($_POST);
    $enabled = pv('enabled', 0);
    $autologin = 1 - pv('autologin', 0); // PAUL!!!
    if ($id == -1) {
        $sql = "
			INSERT INTO `record`
		";
        $where = '';
    } else {
        $sql = "
			UPDATE `record`
		";
        $where = "
			WHERE `id`=$id
		";
    }
    $sql.="
		SET 
			`title`='" . $_POST['T'] . "',
			`url`='" . $_POST['U'] . "',
			`config`='" . $_POST['C'] . "',
			`comment`='" . $_POST['comments'] . "',
			`autologin`=$autologin,
			`active`=$enabled 
		$where
	";
    safe_query($sql);
    if ($id == -1) {
        $id = mysql_insert_id();
    }
    $sql = array();
    $sql[] = "DELETE FROM `host` WHERE `record_id`=" . $id;
    $sql[] = "DELETE FROM `domain` WHERE `record_id`=" . $id;
    $sql[] = "DELETE FROM `extra` WHERE `record_id`=" . $id;
    /* pardon the crytic code here */
    foreach (array('H' => 'host', 'HJ' => 'host', 'D' => 'domain', 'DJ' => 'domain') as $kind => $col) {
        foreach (pv($kind, array()) as $data) {
            if ($data) {
                $js = (strpos($kind, 'J') === false) ? 0 : 1;
                $data = trim($data);
                $sql[] = "
					INSERT INTO `$col`
					SET
						`record_id`=$id,
						`$col`='$data',
						`js`=$js
				";
            }
        }
    }
    /* 'Extra' config options */
    $Xv = pv('Xv');
    foreach (pv('X', array()) as $i => $k) {
        $k = trim($k);
        $v = trim($Xv[$i]);
        $sql[] = "
			INSERT INTO `extra`
			SET
				`record_id`=$id,
				`key`='$k',
				`value`='$v'
		";
    }
    foreach ($sql as $s) {
        safe_query($s);
    }
    return false;
}

function update_sfx($id) {
// All we can do with an SFX record is enable or disable it, or move it from autologin to non.
    if (!is_numeric($id)) {
        die('Non-numeric sfx record id "' . $id . '".');
    }
    strip_array($_POST);
    $enabled = pv('enabled', 0);
    $autologin = pv('autologin', 0);
    $sql = "
		UPDATE `record`
		SET
			`active`=$enabled,
			`autologin`=$autologin
		WHERE
			`id`=$id
	";
    safe_query($sql);
    return false;
}

function update_txt($file) {
// This is not really a database function, but included here so it will be near the other update_*'s
    $target = EZCONFIG_TXT_DIR . $file . '.cfg.txt';
    $content = pv('fc'); // note we don't strip slashes here.
    if (!$content)
        die('missing file content, dying here.');
    if (!copy($target, BACKUP_DIR . "txt/$file.cfg.txt-" . date("Ymd-His"))) {
        die("Failed to create backup file!");
    }
    if (!file_put_contents($target, $content)) {
        die("Error when writing to $target !");
    }
    return false;
}

function importSFX() {
    if (!file_exists(SFX_IMPORT_FILE)) {
        echo('SFX data not found (configured location: ' . SFX_IMPORT_FILE . ')');
        return;
    }
    if (filesize(SFX_IMPORT_FILE) == 0) {
        echo('SFX data file is empty (is the import script configured correctly?)');
        return;
    }
    $fh = fopen(SFX_IMPORT_FILE, 'r');
    if (!$fh) {
        echo('Failed to open ' . SFX_IMPORT_FILE . ' for reading, check permissions.');
        return;
    }
    $log = array();
    while (!feof($fh) && $line = fgets($fh)) {
        $line = trim($line);
        if (strpos($line, 'Title ') === 0 && !strpos($line, 'FREE')) {
            $title = preg_replace('~^Title\s+~', '', $line);
            $url = false;
            $hosts = array();
            $domains = array();
            $extra = array();
            $active = 1;
            $autologin = 1;
            read_stanza($fh, $url, $hosts, $domains, $extra);
            if (!($hosts || $domains)) {
                //nothing useful; skip
                $log[] = 'No host or domain set for ' . $title;
                continue;
            }
            $sql = "SELECT `id`, `source`, `active`, `autologin` FROM `record` WHERE `title` = '" . mysql_real_escape_string($title) . "' LIMIT 1";
            $res = safe_query($sql, true);
            if (mysql_num_rows($res) == 1) {
                $row = mysql_fetch_assoc($res);
                if ($row['source'] != 'SFX') {
                    $log[] = 'Kept local (non-SFX) record for ' . $title;
                    continue;
                }
                $active = $row['active'];
                $autologin = $row['autologin'];
                delete_record($row['id']);
            }
            $sql = "
				INSERT INTO `record`
				SET
					`title` = '" . mysql_real_escape_string($title) . "',
					`url` = '" . mysql_real_escape_string($url) . "',
					`active` = $active,
					`autologin` = $autologin,
					`source`='SFX',
					`comment` = 'Imported from SFX'
			";
            safe_query($sql);
            $rid = mysql_insert_id();
            foreach ($hosts as $host) {
                $h = preg_replace('~^http[^/]*//~', '', mysql_real_escape_string($host[0]));
                $sql = "
					INSERT INTO `host`
					SET
						`record_id` = $rid,
						`host` = '$h',
						`js` = " . ($host[1] ? 1 : 0)
                ;
                safe_query($sql);
            }
            foreach ($domains as $domain) {
                $d = preg_replace('~^http[^/]*//~', '', mysql_real_escape_string($domain[0]));
                $sql = "
					INSERT INTO `domain`
					SET
						`record_id` = $rid,
						`domain` = '$d',
						`js` = " . ($domain[1] ? 1 : 0)
                ;
                safe_query($sql);
            }
            foreach ($extra as $k => $v) {
                foreach ($v as $val) {
                    $sql = "
						INSERT INTO `extra`
						SET
							`record_id` = $rid,
							`key` = '" . mysql_real_escape_string($k) . "',
							`value` = '" . mysql_real_escape_string($val) . "'
					";
                    safe_query($sql);
                }
            }
        }
    }
    fclose($fh);
    gen_sfx_cfg($log);
    global $ALERT_EMAILS;
    if ($log && $ALERT_EMAILS) {
        $subject = "Wondertool SFX Import Log for " . date('Y-m-d g:i:s a') . "\n\n";
        $msg = $subject . implode("\n", $log);
        $headers = "From: do-not-reply@wondertool.org\n\n";
        foreach ($ALERT_EMAILS as $recipient) {
            mail($recipient, $subject, $msg, $headers);
        }
//		echo $msg;
        echo('<p>Message sent to administrator(s).</p>');
    }
}

function gen_sfx_cfg(&$log) {
// Note: we assume all SFX records are autologin-enabled, no matter what the `autologin` column says.
// This is because otherwise we'd have to generate two different include files for SFX. Which we could do,
// but we decided not to. If you need to put an SFX record behind a no-exceptions login, the workaround is to create
// a "normal" record for the resource, which will have priority over the SFX record.
    if (!SFX_ENABLED)
        die('SFX features are disabled. Check your configuration.');
    $outfile = SFX_DIR . 'sfx.cfg.txt';
    $backup = BACKUP_DIR . '/sfx/backup-sfx.cfg.txt-' . date('Ymd-His');
    $fh = fopen($outfile, 'w');
    if (!$fh) {
        die("Cannot open $outfile for writing, check permissions.");
    }
    fwrite($fh, "# EZproxy SFX Include configuration generated " . date('Y-m-d g:i:s a') . "\n\n");
    $sql = "SELECT `id` FROM `record` WHERE `source` = 'SFX' ORDER BY `title` ASC";
    write_cfg($fh, $sql, $log);
    fclose($fh);
}

function gen_ez_cfg(&$log) {
    $outfile = EZCONFIG_DIR . '/config.txt';
    $backup = BACKUP_DIR . '/config/backup-config.txt-' . date('Ymd-His');
    $fh = fopen($outfile, 'w');
    if (!$fh) {
        die("Cannot open $outfile for writing, check permissions.");
    }
    fwrite($fh, "# EZproxy configuration generated " . date('Y-m-d g:i:s a') . "\n\n");
    //include header
    $ih = fopen(EZCONFIG_TXT_DIR . '/header.cfg.txt', 'r');
    while (!feof($ih)) {
        $line = fgets($ih);
        fwrite($fh, $line);
    }
    fclose($ih);
    fwrite($fh, "\n\n#END HEADER\n\n");
    fwrite($fh, "\n\n#--------------------------------------------------------------------------\n");
    fwrite($fh, "#BEGIN REQUIRED LOGIN\n");
    fwrite($fh, "#--------------------------------------------------------------------------\n");
    //generate non-autologin part
    $sql = "SELECT `id` FROM `record` WHERE `source` != 'SFX' AND `autologin`=0 ORDER BY `title` ASC";
    write_cfg($fh, $sql, $log);
    //include autologin (i.e. whitelist IP ranges)
    $ih = fopen(EZCONFIG_TXT_DIR . '/auto.cfg.txt', 'r');
    fwrite($fh, "\n\n#--------------------------------------------------------------------------\n");
    fwrite($fh, "#END REQUIRED LOGIN\n");
    fwrite($fh, "#--------------------------------------------------------------------------\n");
    fwrite($fh, "#BEGIN AUTO\n");
    while (!feof($ih)) {
        $line = fgets($ih);
        fwrite($fh, $line);
    }
    fwrite($fh, "\n\n#END AUTO\n\n");
    fwrite($fh, "\n\n#--------------------------------------------------------------------------\n");
    fwrite($fh, "#BEGIN AUTOMATIC LOGIN\n");
    fwrite($fh, "#--------------------------------------------------------------------------\n");
    fclose($ih);
    if (SFX_ENABLED) {
        fwrite($fh, "IncludeFile\t" . SFX_DIR . "sfx.cfg.txt\n\n");
    }
    $sql = "SELECT `id` FROM `record` WHERE `source` != 'SFX' AND `autologin`=1 ORDER BY `title` ASC";
    write_cfg($fh, $sql, $log);
    fclose($fh);
}

function write_cfg($fh, $sql, &$log) {
    $res = safe_query($sql);
    while ($row = mysql_fetch_assoc($res)) {
        $out = array();
        $comment_out = '';
        $ruleset = get_record($row['id']);
//		var_export($ruleset);
        if ($ruleset['source'] == 'SFX') {
            $doms = array();
            foreach ($ruleset['domains'] as $d) {
                $doms[] = $d[0];
            }
            $sql = "
				SELECT `title`
				FROM `record`,`domain`
				WHERE `domain`.`domain` IN('"
                    . implode("','", $doms)
                    . "')
				AND `domain`.`record_id`=`record`.`id`
				AND `record`.`source` != 'SFX'
			";
            $tres = safe_query($sql);
            if (mysql_num_rows($tres) > 0) {
                $title = mysql_fetch_assoc($tres);
                $title = $title['title'];
                $comment_out = '#';
                $m = 'SFX record "' . $ruleset['title'] . '" superseded by domain for title "' . $title . '"';
                $out[] = '#' . $m;
                $log[] = $m;
            }
        }
        if ($ruleset['active'] == 0) {
            $comment_out = '#';
        }
        if ($ruleset['comment']) {
            $c = explode("\n", $ruleset['comment']);
            $ruleset['comment'] = '#' . implode("\n#", $c);
        }
        if ($ruleset['config']) {
            $out[] = '# Using vendor configuration';
            $fname = VENDOR_DIR . preg_replace('~[^a-z0-9]~i', '', $ruleset['title']) . '.cfg';
            $fc = @file_get_contents($ruleset['config']);
            $skipwrite = false;
            if (!$fc) {
                if (!file_exists($fname)) {
                    $log[] = '*' . $ruleset['title'] . ': Could not retrieve vendor configuration ' . $ruleset['config'] . ' and no local cached copy exists!';
                    $out[] = '#ERROR failed to retrieve vendor configuration for ' . $ruleset['title'] . ' at ' . $ruleset['config'] . ' and no cache exists.';
                } else {
                    $log[] = '*' . $ruleset['title'] . ': Could not retrieve vendor configuration ' . $ruleset['config'] . ', using cached copy';
                    $out[] = '#WARN failed vendor configuration for ' . $ruleset['title'] . ', using cache';
                    $fc = file_get_contents($fname);
                    $skipwrite = true;
                }
            }
            if ($fc) {
                if (!$skipwrite) {
                    $vh = fopen($fname, 'w');
                    if (!$vh)
                        die('Cannot open ' . $fname . ' for writing; please check permissions.');
                    fwrite($vh, $fc);
                    fclose($vh);
                }
                $out[] = $ruleset['comment'];
                $out[] = $comment_out . "Title\t" . $ruleset['title'];
                $out[] = "#Source URL: " . $ruleset['config'];
                $out[] = $comment_out . "IncludeFile " . $fname;
            }
        }else {
            if (array_search(array('Option' => 'DomainCookiesOnly'), $ruleset['extra'])) {
                $out[] = $comment_out . 'Option DomainCookiesOnly';
            }
            $out[] = $ruleset['comment'];
            $out[] = $comment_out . "Title\t" . $ruleset['title'];
            $out[] = $comment_out . "URL\t" . $ruleset['url'];
            foreach ($ruleset['hosts'] as $host) {
                $line = $comment_out . 'Host';
                if ($host[1])
                    $line.='Javascript';
                $line.="\t" . $host[0];
                $out[] = $line;
            }
            foreach ($ruleset['domains'] as $domain) {
                $line = $comment_out . 'Domain';
                if ($domain[1])
                    $line.='Javascript';
                $line.="\t" . $domain[0];
                $out[] = $line;
            }
            foreach ($ruleset['extra'] as $extra) {
                if ($extra[0] && $extra[1]) {
                    if ($extra[1] != 'DomainCookiesOnly') {
                        $line = $comment_out . $extra[0] . "\t" . $extra[1];
                        $out[] = $line;
                    }
                }
            }
        }
        fwrite($fh, implode("\n", $out) . "\n\n");
    }
}

function delete_record($rid) {
    $sql = array();
    $sql[] = "DELETE FROM `record` 	WHERE `id`=$rid";
    $sql[] = "DELETE FROM `host`		WHERE `record_id`=$rid";
    $sql[] = "DELETE FROM `domain` 	WHERE `record_id`=$rid";
    $sql[] = "DELETE FROM `extra`		WHERE `record_id`=$rid";
    foreach ($sql as $s) {
        safe_query($s);
    }
}

function safe_query($sql, $returnonerror=false) {
    $res = mysql_query($sql);
    if (!$res) {
        if ($returnonerror) {
            return false;
        } else {
            echo('
				<pre>MySQL Error
				Query: ' . htmlentities($sql) . '
				Message: ' . htmlentities(mysql_error()) . '
				</pre>
			');
            die();
        }
    }
    return $res;
}

function db_initialize() {
    echo("<p>Initializing database...</p>");
    $sqlfile = WT_DIR . '/schema.sql';
    $sql = "DROP DATABASE IF EXISTS `" . DBNAME . "`";
    mysql_query($sql);
    $sql = "CREATE DATABASE `" . DBNAME . "`";
    if (!mysql_query($sql)) {
        die('Cannot create database "' . DBNAME
                . '". Does the database user have the correct permissions? You can also create the database '
                . 'manually with the file "' . $sqlfile . '".');
    }
    if (!mysql_select_db(DBNAME)) {
        die('Failed to select database ' . DBNAME);
    }
    $src = file_get_contents($sqlfile);
    $sql = explode("\n", $src);
    $cmd = '';
    foreach ($sql as $s) {
        $s = preg_replace('~^\s*\-\-.*~', '', $s);
        $s = preg_replace('~/\*.*?\*/;?~', '', $s);
        $s = trim($s);
        $cmd.=$s;
        if (substr($cmd, -1, 1) == ';') {
            safe_query($cmd);
            $cmd = '';
        }
    }
    echo('<p>...Initialized!</p>');
    if (file_exists(EZCONFIG)) {
        return readOldConfigToDatabase();
    }
    return true;
}

function readOldConfigToDatabase() {
    echo('<p>importing existing configuration...</p>');
    $fh = fopen(EZCONFIG, 'r');
    if (!$fh)
        die('Failed to open ' . EZCONFIG . ' for reading!');
    $count = 0;
    $saveline = false;
    while (!feof($fh)) {
        $line = trim(fgets($fh));
        echo "$line<br />";
        if (preg_match('~^Option\s+DomainCookieOnly~', $line)) {
            // for some effing reason, this directive precedes the stanza, so have to stash it
            $saveline = $line;
        }
        if (preg_match('~^(T(itle)?\s+)~', $line, $m)) {
            $title = preg_replace('~^' . $m[1] . '~', '', $line);
            $url = false;
            $hosts = array();
            $domains = array();
            $extra = array();
            if ($saveline) {
                $extra['Option'][] = 'DomainCookieOnly';
                $saveline = false;
            }
            read_stanza($fh, $url, $hosts, $domains, $extra);
            $sql = "
				INSERT INTO `record`
				SET
					`title` = '" . mysql_real_escape_string($title) . "',
					`url` = '" . mysql_real_escape_string($url) . "',
					`active` = 1,
					`autologin` = 1,
					`comment` = 'Initial configuration import'
			";
            safe_query($sql);
            $rid = mysql_insert_id();
            foreach ($hosts as $host) {
                $sql = "
					INSERT INTO `host`
					SET
						`record_id` = $rid,
						`host` = '" . mysql_real_escape_string($host[0]) . "',
						`js` = " . ($host[1] ? 1 : 0)
                ;
                safe_query($sql);
            }
            foreach ($domains as $domain) {
                $sql = "
					INSERT INTO `domain`
					SET
						`record_id` = $rid,
						`domain` = '" . mysql_real_escape_string($domain[0]) . "',
						`js` = " . ($domain[1] ? 1 : 0)
                ;
                safe_query($sql);
            }
            foreach ($extra as $k => $v) {
                foreach ($v as $val) {
                    $sql = "
						INSERT INTO `extra`
						SET
							`record_id` = $rid,
							`key` = '" . mysql_real_escape_string($k) . "',
							`value` = '" . mysql_real_escape_string($val) . "'
					";
                    safe_query($sql);
                }
            }
            $count++;
            echo('<hr>');
        }
    }
    fclose($fh);
    echo("<p>$count records imported!</p>");
    echo('<p><a href="' . WT_URL . '">HOME</a>');
    exit();
}

function read_stanza($fh, &$url, &$hosts, &$domains, &$extra) {
    while (!feof($fh) && $line = trim(fgets($fh))) {
//		echo "$line<br />";
        list($type, $data) = preg_split('~\s+~', $line, 2);
        $typelc = strtolower($type);
        switch ($typelc) {
            case 'u':
            case 'url':
                if (!$url) {
                    $url = $data;
                    break;
                } else {
                    $typelc = 'host';
                }
            case 'h':
            case 'host':
                $data = strtolower($data);
                if (!array_search(array($data, false), $hosts)) {
                    $hosts[] = array($data, false);
                }
                break;
            case 'hj':
            case 'hostjavascript':
                $data = strtolower($data);
                if (!array_search(array($data, true), $hosts)) {
                    $hosts[] = array($data, true);
                }
                break;
            case 'd':
            case 'domain':
                $data = strtolower($data);
                if (!array_search(array($data, false), $domains)) {
                    $domains[] = array($data, false);
                }
                break;
            case 'dj':
            case 'domainjavascript':
                $data = strtolower($data);
                if (!array_search(array($data, true), $domains)) {
                    $domains[] = array($data, true);
                }
                break;
            default: // 'extra'
                $extra[$type][] = $data;
        }
    }
}